<?php
/**
 * Created by PhpStorm.
 * User: jjj
 * Date: 2017/8/4
 * Time: 14:17
 */

namespace app\common\model\teacher;


use app\admin\model\PageResponse;
use think\Db;
use think\Model;

class TeacherSpeaking extends Model
{
    protected $table = 'teacher_speaking';
    protected $pk = 'id';

    /**
     * 管理端 列表
     *
     * @param int $limit
     * @param int $offset
     * @param null $keywords
     * @return PageResponse
     */
    public static function manageList(int $limit, int $offset, $keywords = null) :PageResponse {

        $filter = $keywords ? " AND title like :kw " : null;
        $params = ['limit' => $limit, 'offset' => $offset];

        if ($keywords) $params['kw'] = "%$keywords%";

        $sql = <<<SQL
SELECT * FROM teacher_speaking
WHERE TRUE {$filter}
ORDER BY id DESC 
LIMIT :limit OFFSET :offset
SQL;
        $list = Db::query($sql, $params);

        $sql = <<<SQL
SELECT count(id) as num FROM teacher_speaking
WHERE TRUE {$filter}
SQL;
        $re = Db::query($sql, $keywords ? ['kw' => "%$keywords%"] : []);
        $total = $re[0]['num'] ?? 0;

        return PageResponse::success($list, $total);
    }

    /**
     * API 列表
     *
     * @param int $limit
     * @param int $offset
     * @param int $teacher_id
     * @param bool $pageTotal
     * @return mixed
     */
    public static function apiList(int $limit, int $offset, int $teacher_id = 0, $pageTotal = false) {

        $where = null;
        $params = ['limit'=>$limit, 'offset'=>$offset];
        if ($teacher_id > 0) {
            $where .= ' AND teacher_id=:teacher_id ';
            $params['teacher_id'] = $teacher_id;
        }
        $sql = <<<SQL
SELECT s.*,t.realname AS teacher_name,t.avatar AS teacher_avatar
FROM teacher_speaking s 
JOIN teacher t ON s.teacher_id=t.id
WHERE TRUE {$where}
ORDER BY s.id DESC 
LIMIT :limit OFFSET :offset
SQL;
        $list = Db::query($sql, $params);

        foreach ($list as & $item) {
            $item['duration'] = intval($item['duration']);
        }

        if (!$pageTotal) return $list;

        $sql = <<<SQL
SELECT count(*) total
FROM teacher_speaking s 
JOIN teacher t ON s.teacher_id=t.id
WHERE TRUE {$where}
SQL;
        unset($params['limit'], $params['offset']);
        $re = Db::query($sql, $params);
        $total = $re[0]['total'] ?? 0;

        return ['list'=>$list,'total'=>$total];
    }

    /**
     * 获取当天的所有语音
     *
     * @param int $teacher_id
     * @return mixed
     */
    public static function todayList(int $teacher_id = 0) {

        $where = null;
        $now = time();
        $params = [
            'st' => date('Y-m-d', $now),
            'et' => date('Y-m-d', $now + 3600*24)
        ];
        if ($teacher_id > 0) {
            $where .= ' AND teacher_id=:teacher_id ';
            $params['teacher_id'] = $teacher_id;
        }

        $sql = <<<SQL
SELECT s.*,t.realname AS teacher_name,t.avatar AS teacher_avatar
FROM teacher_speaking s
JOIN teacher t ON s.teacher_id=t.id
WHERE s.created BETWEEN :st AND :et {$where}
ORDER BY s.id DESC
SQL;

        $list = Db::query($sql, $params);

        return $list;
    }
}